package db;

import beans.Drivers;
import beans.Vehicle;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class VehicleDao extends MySQLConnectionBean {
    private MySQLConnectionBean connection;

    public VehicleDao() {
        connection = new MySQLConnectionBean();
    }

    //搜索
    public List<Vehicle> searchVehicles(Integer vehicleId, String licensePlate,
                                        String brandModel, String color, Date purchaseDate,String status) {
        List<Vehicle> vehicles = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT * FROM vehicle WHERE 1=1");
        List<Object> params = new ArrayList<>();

        // 动态拼接SQL
        if (vehicleId != null) {
            sql.append(" AND VehicleID = ?");
            params.add(vehicleId);
        }
        if (licensePlate != null && !licensePlate.isEmpty()) {
            sql.append(" AND license_plate LIKE ?");
            params.add("%" + licensePlate + "%");
        }
        if (brandModel != null && !brandModel.isEmpty()) {
            sql.append(" AND brand_model LIKE ?");
            params.add("%" + brandModel + "%");
        }
        if (color != null && !color.isEmpty()) {
            sql.append(" AND color LIKE ?");
            params.add("%" + color + "%");
        }
        if (purchaseDate != null) {
            sql.append(" AND purchase_date = ?");
            params.add(new java.sql.Date(purchaseDate.getTime()));
        }
        if (status != null && !status.isEmpty()) {
            sql.append(" AND status = ?");
            params.add(status);
        }

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {

            // 参数绑定
            for (int i = 0; i < params.size(); i++) {
                Object param = params.get(i);
                if (param instanceof Integer) {
                    pstmt.setInt(i + 1, (Integer) param);
                } else if (param instanceof java.sql.Date) {
                    pstmt.setDate(i + 1, (java.sql.Date) param);
                } else {
                    pstmt.setString(i + 1, param.toString());
                }
            }

            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Vehicle vehicle  = extractVehicleFromResultSet(rs);
                vehicles.add(vehicle);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return vehicles;
    }
    // 插入车辆信息
    public boolean insertVehicle(Vehicle vehicle) throws SQLException {
        String sql = "INSERT INTO vehicle (VehicleID, license_plate, brand_model, color, number,purchase_date,remark,status) VALUES (?, ?, ?, ?, ?,?,?,?)";

        try (Connection conn = connection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, vehicle.getVehicleId());
            stmt.setString(2, vehicle.getLicensePlate());
            stmt.setString(3, vehicle.getBrandModel());
            stmt.setString(4, vehicle.getColor());
            stmt.setInt(5, vehicle.getNumber());
            stmt.setDate(6, new java.sql.Date(vehicle.getPurchaseDate().getTime()));
            stmt.setString(7, vehicle.getRemark());
            stmt.setString(8, "空闲");

            return stmt.executeUpdate() > 0;
        }
    }


    // 获取所有车辆
    public List<Vehicle> getAllVehicles() throws SQLException {
        String sql = "SELECT * FROM vehicle";
        List<Vehicle> vehicles = new ArrayList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            while (rs.next()) {
                Vehicle vehicle = new Vehicle();
                vehicle=extractVehicleFromResultSet(rs);
                vehicles.add(vehicle);
            }
        }
        return vehicles;
    }
    public List<Vehicle> getAllVehicle() throws SQLException {
        List<Vehicle> vehicles = new ArrayList<>();
        String sql = "SELECT VehicleID AS vehicleId, license_plate AS licensePlate FROM vehicle"; // 添加需要的字段

        try (Connection conn = connection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                Vehicle vehicle = new Vehicle();
                vehicle.setVehicleId(rs.getInt("VehicleID"));
                vehicle.setLicensePlate(rs.getString("license_plate"));
                // 设置其他需要的属性
                vehicles.add(vehicle);
            }
        }
        return vehicles;
    }

    // 更新车辆信息
    public boolean updateVehicle(Vehicle vehicle) throws SQLException {
        String sql = "UPDATE vehicle SET license_plate=?, brand_model=?, color=?, purchase_date=?, status=?, number=? WHERE VehicleID=?";

        try (Connection conn = connection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, vehicle.getLicensePlate());
            stmt.setString(2, vehicle.getBrandModel());
            stmt.setString(3, vehicle.getColor());
            stmt.setDate(4, new java.sql.Date(vehicle.getPurchaseDate().getTime()));
            stmt.setString(5, vehicle.getStatus());
            stmt.setInt(6, vehicle.getNumber());
            stmt.setInt(7, vehicle.getVehicleId());

            return stmt.executeUpdate() > 0;
        }
    }
    private Vehicle extractVehicleFromResultSet(ResultSet rs) throws SQLException {
        Vehicle vehicle = new Vehicle();
        vehicle.setVehicleId(rs.getInt("VehicleID"));
        vehicle.setLicensePlate(rs.getString("license_plate"));
        vehicle.setBrandModel(rs.getString("brand_model"));
        vehicle.setColor(rs.getString("color"));
        vehicle.setPurchaseDate(rs.getDate("purchase_date"));
        vehicle.setStatus(rs.getString("status"));
        vehicle.setNumber(rs.getInt("number"));
        vehicle.setRemark(rs.getString("remark"));
        return vehicle;
    }

    // 删除车辆
    public boolean deleteVehicle(int vehicleId) throws SQLException {
        String sql = "DELETE FROM vehicle WHERE VehicleID=?";
        System.out.println(vehicleId);

        try (Connection conn = connection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, vehicleId);
            return stmt.executeUpdate() > 0;
        }
    }

    // 获取所有车辆ID
    public List<Integer> getAllVehicleIds() throws SQLException {
        List<Integer> ids = new ArrayList<>();
        String sql = "SELECT VehicleID FROM vehicle";

        try (Connection conn = connection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                ids.add(rs.getInt("VehicleID"));
            }
        }
        return ids;
    }




    // 搜索车辆
    public List<Vehicle> searchVehicles(String keyword) throws SQLException {
        List<Vehicle> vehicles = new ArrayList<>();
        String sql = "SELECT * FROM vehicle WHERE license_plate LIKE ? OR brand_model LIKE ? OR color LIKE ?";

        try (Connection conn = connection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            String searchPattern = "%" + keyword + "%";
            stmt.setString(1, searchPattern);
            stmt.setString(2, searchPattern);
            stmt.setString(3, searchPattern);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Vehicle vehicle  = extractVehicleFromResultSet(rs);
                    vehicles.add(vehicle);
                }
            }
        }
        return vehicles;
    }

    public Vehicle getVehicleById(int vehicleId) throws SQLException {
        String sql = "SELECT * FROM vehicle WHERE VehicleID = ?";
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, vehicleId);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    Vehicle vehicle = new Vehicle();
                    vehicle.setVehicleId(rs.getInt("VehicleID"));
                    vehicle.setLicensePlate(rs.getString("license_plate"));
                    vehicle.setBrandModel(rs.getString("brand_model"));
                    vehicle.setColor(rs.getString("color"));
                    vehicle.setPurchaseDate(rs.getDate("purchase_date"));
                    vehicle.setStatus(rs.getString("status"));
                    vehicle.setNumber(rs.getInt("number"));
                    vehicle.setRemark(rs.getString("remark"));
                    return vehicle;
                }
            }
        }
        return null;
    }

    // 获取所有可用车辆
    public List<Vehicle> getAvailableVehicles() throws SQLException {
        String sql = "SELECT * FROM vehicle WHERE status = '空闲'";
        List<Vehicle> vehicles = new ArrayList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            System.out.println("执行查询可用车辆SQL: " + sql);

            while (rs.next()) {
                Vehicle vehicle = new Vehicle();
                vehicle.setVehicleId(rs.getInt("VehicleID"));
                vehicle.setLicensePlate(rs.getString("license_plate"));
                vehicle.setBrandModel(rs.getString("brand_model"));
                vehicle.setStatus(rs.getString("status"));
                vehicles.add(vehicle);
                System.out.println("找到可用车辆: " + vehicle.getLicensePlate() + " - " + vehicle.getBrandModel());
            }

            System.out.println("查询到 " + vehicles.size() + " 辆可用车辆");
        } catch (SQLException e) {
            System.out.println("查询可用车辆时发生错误: " + e.getMessage());
            e.printStackTrace();
            throw e;
        }
        return vehicles;
    }

}